package com.unsins.test.coherence;

/**
 * Created by IntelliJ IDEA.
 * User: odpsoft
 * Date: 2009-4-24
 * Time: 7:39:37
 */

import com.tangosol.net.cache.CacheStore;
import com.tangosol.util.Base;

import java.sql.*;
import java.util.*;

public class DBCacheStore extends Base implements CacheStore {
    public DBCacheStore(String sTableName) {
        m_sTableName = sTableName;
        configureConnection();
    }

    protected void configureConnection() {
        try {
            Class.forName(DB_DRIVER);
            m_con = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
            m_con.setAutoCommit(true);
            System.out.println("configure successful!");
        } catch (Exception e) {
            throw ensureRuntimeException(e, "Connection failed");
        }
    }

    public String getTableName() {
        return m_sTableName;
    }

    public Connection getConnection() {
        return m_con;
    }

    public Object load(Object oKey) {
        Object oValue = null;
        Connection con = getConnection();
        String sSQL = "SELECT id, value FROM " + getTableName()
                + " WHERE id = ?";
        try {
            PreparedStatement stmt = con.prepareStatement(sSQL);

            stmt.setString(1, String.valueOf(oKey));

            ResultSet rslt = stmt.executeQuery();
            if (rslt.next()) {
                oValue = rslt.getString(2);
                if (rslt.next()) {
                    throw new SQLException("Not a unique key: " + oKey);
                }
            }
            stmt.close();
        } catch (SQLException e) {
            throw ensureRuntimeException(e, "Load failed: key=" + oKey);
        }
        return oValue;
    }

    public void store(Object oKey, Object oValue) {
        Connection con = getConnection();
        String sTable = getTableName();
        String sSQL;

        if (load(oKey) != null) {
            sSQL = "UPDATE " + sTable + " SET value = ? where id = ?";
        } else {
            sSQL = "INSERT INTO " + sTable + " (value, id) VALUES (?,?)";
        }

        try {
            PreparedStatement stmt = con.prepareStatement(sSQL);
            int i = 0;
            stmt.setString(++i, String.valueOf(oValue));
            stmt.setString(++i, String.valueOf(oKey));
            stmt.executeUpdate();
            stmt.close();
        } catch (SQLException e) {
            throw ensureRuntimeException(e, "Store failed: key=" + oKey);
        }
    }

    public void erase(Object oKey) {
        Connection con = getConnection();
        String sSQL = "DELETE FROM " + getTableName() + " WHERE id=?";
        try {
            PreparedStatement stmt = con.prepareStatement(sSQL);

            stmt.setString(1, String.valueOf(oKey));
            stmt.executeUpdate();
            stmt.close();
        }
        catch (SQLException e) {
            throw ensureRuntimeException(e, "Erase failed: key=" + oKey);
        }
    }

    public void eraseAll(Collection colKeys) {
        throw new UnsupportedOperationException();
    }


    public Map loadAll(Collection colKeys) {
        throw new UnsupportedOperationException();
    }

    public void storeAll(Map mapEntries) {
        throw new UnsupportedOperationException();
    }

    public Iterator keys() {
        Connection con = getConnection();
        String sSQL = "SELECT id FROM " + getTableName();
        List list = new LinkedList();

        try {
            PreparedStatement stmt = con.prepareStatement(sSQL);
            ResultSet rslt = stmt.executeQuery();
            while (rslt.next()) {
                Object oKey = rslt.getString(1);
                list.add(oKey);
            }
            stmt.close();
        }
        catch (SQLException e) {
            throw ensureRuntimeException(e, "Iterator failed");
        }

        return list.iterator();
    }

    protected Connection m_con;
    protected String m_sTableName;
    private static final String DB_DRIVER = "oracle.jdbc.OracleDriver";
    private static final String DB_URL = "jdbc:oracle:thin:@192.168.1.20:1521:inst20";
    private static final String DB_USERNAME = "unsins";
    private static final String DB_PASSWORD = "password";
}
